/**
 * 
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import model.HOC_KY_KHHTM;
import model.KeHoachHocTapMauM;

import util.DAL;
import util.MyConnectUnit;

public class HOC_KY_KHHTD {
private MyConnectUnit conn;
	
	/**
	 * @function constructor CTDTD
	 * @throws Exception 
	 */
	public HOC_KY_KHHTD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}

	/**
	 * @function get all CTDT
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAlls(int id_khht, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<HOC_KY_KHHTM> ls = new ArrayList<HOC_KY_KHHTM>();
		ResultSet rs = this.conn.Selects(
						"id_hoc_ky_khht, stt, hk.ten, hk.id_khht_mau, kh.ten, so_tc, so_tc_bb, so_tc_tc, so_tc_dk",
						"HOC_KY_KHHT hk, KHHT_MAU kh",
						"hk.id_khht_mau = kh.id_khht_mau AND kh.id_khht_mau = " + id_khht,
						"stt ASC",
						limit);
		HOC_KY_KHHTM l = null;
		while (rs.next()) {
			l = new HOC_KY_KHHTM();
			l.setId_hoc_ky_khht(rs.getInt("id_hoc_ky_khht"));
			l.setStt(rs.getString("stt"));
			l.setTen(rs.getString("hk.ten"));
			
			l.setId_khht_mau(rs.getInt("hk.id_khht_mau"));
			KeHoachHocTapMauM kh = new KeHoachHocTapMauM();
			kh.setTen(rs.getString("kh.ten"));
			l.setTen_khht_mau(kh);
			
			l.setSo_tc(rs.getString("so_tc"));
			l.setSo_tc_bb(rs.getString("so_tc_bb"));
			l.setSo_tc_tc(rs.getString("so_tc_tc"));
			l.setSo_tc_dk(rs.getString("so_tc_dk"));
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all CTDT
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAlls(String ten, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<HOC_KY_KHHTM> ls = new ArrayList<HOC_KY_KHHTM>();
		ResultSet rs = this.conn.Selects(
						"id_hoc_ky_khht, stt, hk.ten, hk.id_khht_mau, kh.ten, so_tc, so_tc_bb, so_tc_tc, so_tc_dk",
						"HOC_KY_KHHT hk, KHHT_MAU kh",
						"hk.id_khht_mau = kh.id_khht_mau AND hk.ten LIKE '%" + ten + "%'",
						"stt ASC",
						limit);
		HOC_KY_KHHTM l = null;
		while (rs.next()) {
			l = new HOC_KY_KHHTM();
			l.setId_hoc_ky_khht(rs.getInt("id_hoc_ky_khht"));
			l.setStt(rs.getString("stt"));
			l.setTen(rs.getString("hk.ten"));
			
			l.setId_khht_mau(rs.getInt("hk.id_khht_mau"));
			KeHoachHocTapMauM kh = new KeHoachHocTapMauM();
			kh.setTen(rs.getString("kh.ten"));
			l.setTen_khht_mau(kh);
			
			l.setSo_tc(rs.getString("so_tc"));
			l.setSo_tc_bb(rs.getString("so_tc_bb"));
			l.setSo_tc_tc(rs.getString("so_tc_tc"));
			l.setSo_tc_dk(rs.getString("so_tc_dk"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all CTDT
	 * @param Condition
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAll(String Condition, String Limit, String OrderBy) throws Exception {
		ArrayList<HOC_KY_KHHTM> ls = new ArrayList<HOC_KY_KHHTM>();
		ResultSet rs = this.conn.Select("HOC_KY_KHHT", Condition, Limit, OrderBy);
		HOC_KY_KHHTM l = null;
		while (rs.next()) {
			l = new HOC_KY_KHHTM();
			l.setId_hoc_ky_khht(rs.getInt("id_hoc_ky_khht"));
			l.setStt(rs.getString("stt"));
			l.setTen(rs.getString("ten"));
			l.setId_khht_mau(rs.getInt("id_khht_mau"));
			l.setSo_tc(rs.getString("so_tc"));
			l.setSo_tc_bb(rs.getString("so_tc_bb"));
			l.setSo_tc_tc(rs.getString("so_tc_tc"));
			l.setSo_tc_dk(rs.getString("so_tc_dk"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all HOC_KY_KHHT with Condition + Limit
	 * @param Condition
	 * @param Limit
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAll(String Condition, String Limit) throws Exception {
		return getAll(Condition, Limit, null);
	}
	
	/**
	 * @function get all HOC_KY_KHHT with Condition
	 * @param Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}

	/**
	 * @function get all HOC_KY_KHHT no Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> getAll() throws Exception {
		return getAll(null);
	}

	/**
	 * @function search ten day du CTDT
	 * @param ten
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> searchHK_KHHT(String ten) throws Exception {
		ArrayList<HOC_KY_KHHTM> list = getAll("ten LIKE '%" + ten + "%'");
		return list;
	}
	
	/**
	 * @function load HOC_KY_KHHT
	 * @param ten
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HOC_KY_KHHTM> loadHK_KHHT(int id) throws Exception {
		ArrayList<HOC_KY_KHHTM> list = getAll("id_khht_mau =" + id);
		return list;
	}

	/**
	 * @function insert CTDT
	 * @param ctdt
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(HOC_KY_KHHTM hoc_ky_khht) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("stt", hoc_ky_khht.getStt());
		map.put("ten", hoc_ky_khht.getTen());
		map.put("id_khht_mau", hoc_ky_khht.getId_khht_mau());
		map.put("so_tc", hoc_ky_khht.getSo_tc());
		map.put("so_tc_bb", hoc_ky_khht.getSo_tc_bb());
		map.put("so_tc_tc", hoc_ky_khht.getSo_tc_tc());
		map.put("so_tc_dk", hoc_ky_khht.getSo_tc_dk());
		return this.conn.Insert("HOC_KY_KHHT", map);
	}

	/**
	 * @function update CTDT
	 * @param ctdt
	 * @return
	 * @throws Exception
	 */
	public boolean Update(HOC_KY_KHHTM hoc_ky_khht) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("id_hoc_ky_khht", hoc_ky_khht.getId_hoc_ky_khht());
		map.put("stt", hoc_ky_khht.getStt());
		map.put("ten", hoc_ky_khht.getTen());
		map.put("id_khht_mau", hoc_ky_khht.getId_khht_mau());
		map.put("so_tc", hoc_ky_khht.getSo_tc());
		map.put("so_tc_bb", hoc_ky_khht.getSo_tc_bb());
		map.put("so_tc_tc", hoc_ky_khht.getSo_tc_tc());
		map.put("so_tc_dk", hoc_ky_khht.getSo_tc_dk());
		return this.conn.Update("HOC_KY_KHHT", map, "id_hoc_ky_khht = " + hoc_ky_khht.getId_hoc_ky_khht());
	}

	/**
	 * @function delete CTDT
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id) throws Exception {
		return this.conn.Delete("HOC_KY_KHHT", "id_hoc_ky_khht = " + id);
	}
}
